﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using SQLTask2;


namespace SQLTask2
{
    public partial class CurrenciesListForm : Form
    {
        private MySqlConnection pConn;
        private MySqlDataAdapter da = null;
        private DataTable ds = null;
        private string[] queries = {//Fill main grid
                                    @"SELECT Currency_ID AS `ID`,
                                             currencies.Name AS `Валюта`,
                                             countries.Name AS `Страна` 
                                     FROM currencies 
                                     JOIN countries ON currencies.Country_ID=countries.Country_ID",
                                    //Fill countries combobox
                                    @"SELECT Country_ID, Name FROM countries;",
                                    @"INSERT INTO countries (Name) VALUES ('{0}')"
                                   };

        public CurrenciesListForm(MySqlConnection conn)
        {
            InitializeComponent();
            pConn = conn;
            ds = new DataTable();
        }

        private void UpdateMainTable()
        {
            try
            {
                ds.Clear();
                da.Fill(ds);
                dataGridView1.Columns[0].Width = 35;
                dataGridView1.Refresh();
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }
        }

        private void CurrenciesListForm_Load(object sender, EventArgs e)
        {
            try
            {
                da = new MySqlDataAdapter(queries[0], pConn);
                dataGridView1.DataSource = ds;
                UpdateMainTable();
                DataTable ds2 = new DataTable();
                (new MySqlDataAdapter(queries[1], pConn)).Fill(ds2);
                comboBox1.DataSource = ds2;
                comboBox1.DisplayMember = "Name";
                comboBox1.ValueMember = "Country_ID";
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
           
           // MySqlCommand cmd = new MySqlCommand(query, pConn);
            try
            {
                int? countryID = (int?)comboBox1.SelectedValue;
                if (countryID == null)
                {
                    using (MySqlCommand cmd = new MySqlCommand(String.Format(queries[2], comboBox1.Text), pConn))
                    {
                        int k = cmd.ExecuteNonQuery();
                    }
                    using (MySqlCommand cmd = new MySqlCommand("SELECT last_insert_id()", pConn))
                    {
                        countryID = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }


                string query = @"INSERT INTO currencies (Name, Country_ID) VALUES ('" + textBox1.Text + "','" + countryID + "');";

                using (MySqlCommand cmd = new MySqlCommand(query, pConn))
                {
                    cmd.ExecuteNonQuery();
                    UpdateMainTable();
                }
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }
        }
    }
}
